insert overwrite table jms_dm.dm_aviation_order_detail_dt partition(dt)
SELECT
     waybill_no                     -- 运单号
    ,collect_time                   -- 揽收时间
    ,operation_date                 -- 发货上报日期
    ,operation_time                 -- 发货上报时间
    ,package_code                   -- 包号
    ,send_scan_time                 -- 航空发件时间
    ,arrive_scan_time               -- 航空到件时间
    ,pick_network_code              -- 订单所属网点编码
    ,pick_network_name              -- 订单所属网点名称
    ,pick_agent_area_name           -- 订单所属代理区
    ,origin_center_code             -- 航空始发机构编号
    ,origin_center_name             -- 航空始发机构名称
    ,origin_agent_area_id           -- 航空始发代理区ID
    ,origin_agent_area_code         -- 航空始发代理区编号
    ,origin_agent_area_name         -- 航空始发代理区
    ,origin_airport                 -- 始发机场
    ,booking_space_no               -- 订舱号
    ,destination_agent_area_id      -- 航空目的地代理区ID
    ,destination_agent_area_code    -- 航空目的地代理区编码
    ,destination_agent_area_name    -- 航空目的地代理区
    ,destination_center_code        -- 航空目的地机构编号
    ,destination_center_name        -- 航空目的地机构名称
    ,destination_airport            -- 目的机场
    ,package_inside_charge_weight   -- 内部计费重量
    ,dispatch_time                  -- 派件时间
    ,dispatch_agent_code            -- 派件代理区
    ,dispatch_agent_name            -- 派件代理区
    ,dispatch_network_code          -- 派件网点编码
    ,dispatch_network_name          -- 派件网点名称
    ,sender_province_id             -- 寄件省ID
    ,sender_province_name           -- 寄件省
    ,sender_city_id                 -- 寄件市ID
    ,sender_city_name               -- 寄件市
    ,sender_area_id                 -- 寄件区ID
    ,sender_area_name               -- 寄件区
    ,receiver_province_id           -- 目的省ID
    ,receiver_province_name         -- 目的省
    ,receiver_city_id               -- 目的市ID
    ,receiver_city_name             -- 目的市
    ,receiver_area_id               -- 目的区ID
    ,receiver_area_name             -- 目的区
    ,plan_cainiao_platform_date     -- 菜鸟规划应签收日期
    ,cainiao_platform_effect        -- 菜鸟平台时效
    ,is_pres_sign                   -- 时效签收
    ,operation_date                 -- 发货上报日期(分区日期)
FROM
(
    select  
        tasr.waybill_no                           -- 运单号
        ,tasr.collect_time                        -- 揽收时间
        ,to_date(tsgr.operation_time) as operation_date -- 发货上报日期
        ,tsgr.operation_time                      -- 发货上报时间
        ,barscan.package_code                     -- 包号
        ,tasr.send_scan_time                      -- 航空发件时间
        ,tasr.arrive_scan_time                    -- 航空到件时间
        ,tasr.pick_network_code                   -- 订单所属网点编码
        ,tasr.pick_network_name                   -- 订单所属网点名称
        ,tasr.pick_agent_area_name                -- 订单所属代理区
        ,tsgr.origin_center_code                  -- 航空始发机构编号
        ,tsgr.origin_center_name                  -- 航空始发机构名称
        ,tsgr.origin_agent_area_id                -- 航空始发代理区ID
        ,tsgr.origin_agent_area_code              -- 航空始发代理区编号
        ,tsgr.origin_agent_area_name              -- 航空始发代理区
        ,tsgr.origin_airport                      -- 始发机场
        ,tasr.booking_space_no                    -- 订舱号
        ,ttd.destination_agent_area_id            -- 航空目的地代理区ID
        ,ttd.destination_agent_area_code          -- 航空目的地代理区code
        ,ttd.destination_agent_area_name          -- 航空目的地代理区
        ,ttd.destination_center_code              -- 航空目的地机构编号
        ,ttd.destination_center_name              -- 航空目的地机构名称
        ,tsgr.destination_airport                 -- 目的机场
        ,tasr.package_inside_charge_weight        -- 内部计费重量
        ,waybill.dispatch_time                    -- 派件时间
        ,dim_network.code         as dispatch_agent_code -- 派件代理区编码
        ,dim_network.name         as dispatch_agent_name -- 派件代理区名称
        ,waybill.dispatch_network_code            -- 派件网点编码
        ,waybill.dispatch_network_name            -- 派件网点名称
        ,waybill.sender_province_id               -- 寄件省ID
        ,waybill.sender_province_name             -- 寄件省
        ,waybill.sender_city_id                   -- 寄件市ID
        ,waybill.sender_city_name                 -- 寄件市
        ,waybill.sender_area_id                   -- 寄件区ID
        ,waybill.sender_area_name                 -- 寄件区
        ,waybill.receiver_province_id             -- 目的省ID
        ,waybill.receiver_province_name           -- 目的省
        ,waybill.receiver_city_id                 -- 目的市ID
        ,waybill.receiver_city_name               -- 目的市
        ,waybill.receiver_area_id                 -- 目的区ID
        ,waybill.receiver_area_name               -- 目的区
        ,reach_detail.plan_cainiao_platform_date  -- 菜鸟规划应签收日期
        ,reach_detail.cainiao_platform_effect     -- 菜鸟平台时效
        ,reach_detail.is_pres_sign                -- 时效签收
        ,ROW_NUMBER () OVER (PARTITION BY tsgr.booking_space_no,tasr.waybill_no ORDER BY ttd.TAKE_DELIVERY_TIME asc ) AS SUM_ROW_NUM
    FROM  jms_dwd.dwd_tab_aviation_send_record_base_dt tasr
    INNER JOIN jms_dim.dim_tab_send_goods_report_base tsgr
                ON tasr.booking_space_no = tsgr.booking_space_no
               and tsgr.flight_date >= date_add('{{ execution_date | cst_ds }}',-11)
               and tsgr.flight_date <= date_add('{{ execution_date | cst_ds }}',2)
    LEFT JOIN jms_dim.dim_tab_take_delivery_base ttd
                ON tsgr.id = ttd.send_goods_report_id
    left join (
        select 
            waybill_no 
            ,shipment_no  -- 任务单、订舱号
            ,max(package_code) as package_code 
        from(
            select 
                waybill_no 
                ,shipment_no  -- 任务单、订舱号
                ,package_code
            from  
                jms_dwd.dwd_tab_barscan_send_base_dt
            where 
                dt >= date_add('{{ execution_date | cst_ds }}',-11)
                and dt <= '{{ execution_date | cst_ds }}'
                and package_code is not null 
            
            union all 

            select 
                waybill_no
                ,shipment_no  -- 任务单、订舱号 
                ,package_code
            from 
                jms_dwd.dwd_tab_barscan_send_base_dt
            where 
                dt >= date_add('{{ execution_date | cst_ds }}',-11)
                and dt <= '{{ execution_date | cst_ds }}'
                and package_code is not null    
        ) a 
        group by 
            waybill_no 
            ,shipment_no  -- 任务单、订舱号
    ) barscan
        on tasr.booking_space_no = barscan.shipment_no
       and tasr.waybill_no = barscan.waybill_no
    left join 
        jms_dwd.dwd_yl_oms_oms_waybill_incre_dt waybill 
        on tasr.waybill_no = waybill.waybill_no
       and waybill.dt >= date_add('{{ execution_date | cst_ds }}',-11) and waybill.dt <= '{{ execution_date | cst_ds }}'
    left join 
        jms_dm.dm_prescription_reach_details_dt reach_detail
            on tasr.waybill_no = reach_detail.waybill_no 
           and reach_detail.dt >= date_add('{{ execution_date | cst_ds }}',-11) and reach_detail.dt <= '{{ execution_date | cst_ds }}'
    left join 
        jms_dim.dim_network_whole_massage dim_network
            on waybill.dispatch_network_code = dim_network.code
    where tasr.dt >= date_add('{{ execution_date | cst_ds }}',-10) and tasr.dt <= '{{ execution_date | cst_ds }}'
    and to_date(operation_time) >= date_add('{{ execution_date | cst_ds }}',-10) and to_date(operation_time) <= '{{ execution_date | cst_ds }}'
)
WHERE SUM_ROW_NUM = 1
distribute by 1
;